﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace SisLegal.Model
{
    public class ExpedientFiled
    {
        public int ID { get; set; }
        public string NroExpedient { get; set; }
        public int TypeProcess { get; set; }
        public string TypeProcessName { get; set; }
        public int ViaProcedural { get; set; }
        public string ViaProceduralName { get; set; }
        public DateTime DateBegin { get; set; }
        public DateTime DateEnd { get; set; }
        public int ResultSentences { get; set; }
        public string Juzgado { get; set; }
        public string Pretencion { get; set; }
        public string Demandante { get; set; }
        public string Demandado { get; set; }
        public int ParticipationSeal { get; set; }
        public string LawyerOther { get; set; }
        public string Sumilla { get; set; }
        public string Observaciones { get; set; }


        public ExpedientFiled()
        {
            this.ID = 0;
            this.NroExpedient = "";
            this.TypeProcess = 0;
            this.TypeProcessName = "";
            this.ViaProcedural = 0;
            this.ViaProceduralName = "";
            this.DateBegin = new DateTime();
            this.DateEnd = new DateTime();
            this.ResultSentences = 0;
            this.Juzgado = "";
            this.Pretencion = "";
            this.Demandante = "";
            this.Demandado = "";
            this.ParticipationSeal = 0;
            this.LawyerOther = "";
            this.Sumilla = "";
            this.Observaciones = "";

        }
    }

    public class ExpedientFiledManager
    {
        public List<ExpedientFiled> GetAll()
        {
            List<ExpedientFiled> result = new List<ExpedientFiled>();
            string sqlQuery = String.Format("SELECT * FROM Expedient ORDER BY ID");
            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            SqlDataReader dataReader = command.ExecuteReader();
            ExpedientFiled process = null;
            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    process = new ExpedientFiled();
                    process.ID = Convert.ToInt32(dataReader["ID"]);
                    process.NroExpedient = dataReader["NroFiscalFolder"].ToString();
                    process.TypeProcess = Convert.ToInt32(dataReader["TypeProcess"]);
                    process.TypeProcessName = ((eTypeProcess)process.TypeProcess).ToString();
                    process.ViaProcedural = Convert.ToInt32(dataReader["ViaProcedural"]);
                    process.DateBegin = new DateTime();
                    process.DateEnd = new DateTime();
                    process.ResultSentences = Convert.ToInt32(dataReader["ResultSentences"]);
                    process.Juzgado = dataReader["Juzgado"].ToString();
                    process.Pretencion = dataReader["Pretencion"].ToString();
                    process.Demandante = dataReader["Demandante"].ToString();
                    process.Demandado = dataReader["Demandado"].ToString();
                    process.ParticipationSeal = Convert.ToInt32(dataReader["ParticipationSeal"]);
                    process.LawyerOther = dataReader["LawyerOther"].ToString();
                    process.Sumilla = dataReader["Sumilla"].ToString();
                    process.Observaciones = dataReader["Observaciones"].ToString();
                    result.Add(process);

                }
            }
            return result;
        }
        public Int32 Count()
        {            
            string sqlQuery = String.Format("  SELECT COUNT(ID) AS total FROM Expedient");
            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            command.CommandType = CommandType.Text;
            Int32 countStart = (Int32)command.ExecuteScalar();
            return countStart;
        }

        public List<ExpedientFiled> Paginate(Int32 pag)
        {
            List<ExpedientFiled> result = new List<ExpedientFiled>();
            
            string sqlQuery = String.Format("SELECT tbl.ID, tbl.NroExpedient, tbl.TypeProcess, tbl.ViaProcedural, tbl.DateBegin, " +
            "tbl.DateEnd, tbl.ResultSentences, tbl.Juzgado, tbl.Pretencion, tbl.Demandante, tbl.Demandado, " +
            "tbl.ParticipationSeal, tbl.LawyerOther, tbl.Sumilla, tbl.Observaciones, vp.Name, vp.ProcessID " +
            "FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID) AS NUMBER, * FROM Expedient e)as tbl " +
            "INNER JOIN ViaProcedural vp ON tbl.ViaProcedural = vp.ID " +
            "WHERE NUMBER BETWEEN((" + Convert.ToString(pag) + "-1) * (10 +1)) AND (" + Convert.ToString(pag) + " * 10)" +
            "ORDER BY tbl.ID ");


            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            SqlDataReader dataReader = command.ExecuteReader();
            ExpedientFiled process = null;
            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    process = new ExpedientFiled();
                    process.ID = Convert.ToInt32(dataReader["ID"]);
                    process.NroExpedient = dataReader["NroExpedient"].ToString();
                    process.TypeProcess = Convert.ToInt32(dataReader["TypeProcess"]);
                    process.TypeProcessName = ((eTypeProcess)process.TypeProcess).ToString();
                    process.ViaProceduralName = dataReader["name"].ToString();
                    process.ViaProcedural = Convert.ToInt32(dataReader["ViaProcedural"]);
                    process.DateBegin = new DateTime();
                    process.DateEnd = new DateTime();
                    process.ResultSentences = Convert.ToInt32(dataReader["ResultSentences"]);
                    process.Juzgado = dataReader["Juzgado"].ToString();
                    process.Pretencion = dataReader["Pretencion"].ToString();
                    process.Demandante = dataReader["Demandante"].ToString();
                    process.Demandado = dataReader["Demandado"].ToString();
                    process.ParticipationSeal = Convert.ToInt32(dataReader["ParticipationSeal"]);
                    process.LawyerOther = dataReader["LawyerOther"].ToString();
                    process.Sumilla = dataReader["Sumilla"].ToString();
                    process.Observaciones = dataReader["Observaciones"].ToString();
                    result.Add(process);
                }
            }
            return result;
        }

        public int Save(ExpedientFiled obj)
        {
            string createQuery = String.Format("Insert into Expedient (NroExpedient "+
           ",TypeProcess, ViaProcedural,DateBegin,DateEnd,ResultSentences,Juzgado "+
           ",Pretencion, Demandante,Demandado,ParticipationSeal,LawyerOther,Sumilla,Observaciones) "+
           " Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}');"
                + "Select @@Identity", obj.NroExpedient, 
                obj.TypeProcess, obj.ViaProcedural, obj.DateBegin, obj.DateEnd, obj.ResultSentences, obj.Juzgado,
                obj.Pretencion, obj.Demandante, obj.Demandado, obj.ParticipationSeal,obj.LawyerOther, obj.Sumilla, obj.Observaciones);

            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = null;

            command = new SqlCommand(createQuery, connection);

            int saveID = 0;
            try
            {
                var commandResult = command.ExecuteScalar();
                if (commandResult != null)
                {
                    saveID = Convert.ToInt32(commandResult);
                }
            }
            catch (Exception ex)
            {

            }

            command.Dispose();
            connection.Close();
            connection.Dispose();

            return saveID;
        }

        public ExpedientFiled GetById(int articleId)
        {


            ExpedientFiled process = new ExpedientFiled();
            string sqlQuery = String.Format("select * from Expedient where ID={0}", articleId);
            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            SqlDataReader dataReader = command.ExecuteReader();

            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {                    
                    process.ID = Convert.ToInt32(dataReader["ID"]);
                    process.NroExpedient = dataReader["NroExpedient"].ToString();
                    process.TypeProcess = Convert.ToInt32(dataReader["TypeProcess"]);
                    //process.TypeProcessName = ((eTypeProcess)process.TypeProcess).ToString();
                    //process.ViaProceduralName = dataReader["name"].ToString();
                    process.ViaProcedural = Convert.ToInt32(dataReader["ViaProcedural"]);
                    process.DateBegin = Convert.ToDateTime(dataReader["DateBegin"]);
                    process.DateEnd = Convert.ToDateTime(dataReader["DateEnd"]);
                    process.ResultSentences = Convert.ToInt32(dataReader["ResultSentences"]);
                    process.Juzgado = dataReader["Juzgado"].ToString();
                    process.Pretencion = dataReader["Pretencion"].ToString();
                    process.Demandante = dataReader["Demandante"].ToString();
                    process.Demandado = dataReader["Demandado"].ToString();
                    process.ParticipationSeal = Convert.ToInt32(dataReader["ParticipationSeal"]);
                    process.LawyerOther = dataReader["LawyerOther"].ToString();
                    process.Sumilla = dataReader["Sumilla"].ToString();
                    process.Observaciones = dataReader["Observaciones"].ToString();
                }
            }

            return process;
        }

        public int Update(ExpedientFiled obj)
        {
            string updateQuery = String.Format("UPDATE Expedient SET NroExpedient = '{0}'" +
           ",TypeProcess ='{1}', ViaProcedural ='{2}', DateBegin ='{3}', DateEnd ='{4}', ResultSentences ='{5}', Juzgado = '{6}'" +
           ",Pretencion ='{7}', Demandante ='{8}', Demandado ='{9}', ParticipationSeal ='{10}', LawyerOther ='{11}', Sumilla ='{12}', Observaciones ='{13}' WHERE ID = {14} " ,
            obj.NroExpedient,obj.TypeProcess, obj.ViaProcedural, obj.DateBegin, obj.DateEnd, obj.ResultSentences, obj.Juzgado,
                obj.Pretencion, obj.Demandante, obj.Demandado, obj.ParticipationSeal, obj.LawyerOther, obj.Sumilla, obj.Observaciones, obj.ID);


           

            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = null;
            command = new SqlCommand(updateQuery, connection);

            int saveID = 0;
            try
            {
                var commandResult = command.ExecuteScalar();
                if (commandResult != null)
                {
                    saveID = Convert.ToInt32(commandResult);
                }
                else
                {
                    saveID = obj.ID;
                }
            }
            catch (Exception ex)
            {

            }
            command.Dispose();
            connection.Close();
            connection.Dispose();

            return saveID;
        }
        
        

        public bool Delete(int articleId)
        {
            bool result = false;
            string sqlQuery = String.Format("delete from Expedient where ID = {0}", articleId);

            SqlConnection connection = new SqlConnection(Util.ConnKLegal);
            connection.Open();
            SqlCommand command = new SqlCommand(sqlQuery, connection);
            int rowsDeletedCount = command.ExecuteNonQuery();
            if (rowsDeletedCount != 0)
                result = true;
            command.Dispose();
            connection.Close();
            connection.Dispose();
            return result;
        }
    }
}